<?php

$dbHost= "localhost";
$dbUser = "root";
$dbPassword = "";
$link_id;

$studentList = array(
		array("studentId" => 1, "firstName" => "Ferris",  "lastName" => "Bueller"),
		array("studentId" => 2, "firstName" => "Cameron", "lastName" => "Frye"),
		array("studentId" => 3, "firstName" => "Sloan",   "lastName" => "Lake"),
		array("studentId" => 4, "firstName" => "Ben",     "lastName" => "Stein"),
		array("studentId" => 5, "firstName" => "Drew",    "lastName" => "Goldberg")
);


$userList = array(
		array("user" => "admin", "password" => "pwad"),
		array("user" => "user1", "password" => "pw1"),
		array("user" => "user2", "password" => "pw2")
		);

/*
 * Database connections section:
 * dbConnect: opens database connection
 * dbClose: closes databse connection
 * */
function dbConnect() {
	global $dbHost, $dbUser, $dbPassword, $link_id;
	$link_id_temp = mysql_connect($dbHost, $dbUser, $dbPassword);
	if($link_id_temp){
		$link_id = $link_id_temp;
		return $link_id;
	}
}


function dbClose($link_id) {
	mysql_close($link_id);
}


/*
 *	 Basic Database functionality section:
 * 	 dbCreate: create a new database
 *   dbSelect: select a database
 * 	 dbDrop: drop a database	
 * */
function dbCreate($dbName){
	global $link_id;
	mysql_query("CREATE DATABASE " . $dbName, $link_id);
}

function dbSelect($dbName){
	mysql_select_db($dbName);
}

function dbDrop($dbName) {
	mysql_query("DROP DATABASE " . $dbName);
}



/*
 * 	Basic Table functionality section:
 * 	dbCreateTable: create a new table
 * 	dbDroptable: drop a table
 * 	dbInializeTable: initialize table with students
 *  dbShowTables: display all the tables in the database
 *  dbInsertNewStudent: adds new student to the table
 * **/
function dbCreateTable($dbTableName) {
	mysql_query("CREATE TABLE " . $dbTableName . "(
			studentId INT,
			firstName VARCHAR(30),
			lastName VARCHAR(30))");
}

function dbCreateUserTable() {
	mysql_query("CREATE TABLE USERS (user VARCHAR(30), password VARCHAR(30))");
}

function dbDropTable($dbTableName){
	mysql_query("DROP TABLE " . $dbTableName);
}

function dbInitializeTable($dbName, $dbTableName) {
	global $studentList;
	$sl = $studentList;
	foreach($sl as $student) {
		$insert = "INSERT INTO  $dbTableName (studentId, firstName, lastName) VALUES($student[studentId],'$student[firstName]', '$student[lastName]')";
		mysql_query($insert);
	}
}


function dbInitializeUserTable(){
	global $userList;
	$ul = $userList;
	foreach($ul as $user){
		$insert = "INSERT INTO USERS (user, password) VALUES('$user[user]', '$user[password]')";
		mysql_query($insert);
	}
}


function dbShowTablesInDatabase(){
	mysql_query("SHOW TABLES");
}



function dbInsertNewStudent($dbTableName, $studentId, $firstName, $lastName){
 	//insert into attendance(lastName) values('test');Drop Table attendance;
	//$insert = 'INSERT INTO attendance (lastName) VALUES ("Robert");DROP Table attendances';
	//$insert = "DROP Table attendance";
	//$insert = "SELECT * FROM attendance WHERE (lastName = '" . $lastName . "')";
	//$insert = "INSERT INTO attendance (lastName) VALUES ('Rob'); DROP TABLE attendance; --')";
	$insert = "INSERT INTO $dbTableName (studentId, firstName, lastName) values($studentId, '$firstName', '$lastName')";
	mysql_query($insert);
}




/*
 * Special Database functionaly:
 * dbClean: drops table and drops database and the recreates the database, table, and initializes the table with values
 * 
 * **/
function dbClean($dbName, $dbTableName) {
	global $link_id;
	
	if(!$link_id)
		dbConnect();
	
	dbDropTable($dbTableName);
	dbDropTable("USERS");
	dbDrop($dbName);
	dbCreate($dbName);
	dbSelect($dbName);
	
	if(!dbShowTablesInDatabase()){
		dbCreateTable($dbTableName);
		dbCreateUserTable();
	}
	dbInitializeTable($dbName, $dbTableName);
	dbInitializeUserTable();
	dbClose($link_id);
	echo "Database: $dbName and Table: $dbTableName have been created and initialized successfully.<br />";
}

?>